package net.comm.group.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class GForumServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from GForum where GForumID=?";
	}

	
	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into GForum (  gForumID,  groupID,  villageID, title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up,  good,  upCnt, downCnt, tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update GForum  set groupID=?,  villageID=?, title=?, author=?,  frsource=?,  content=?,  intro=?," +
				" fileID=?,  firsort=?,  secsort=?,  thisort=?,  status=?, type=?,  up=?,  good=?, upCnt=?, downCnt=?, " +
				" tags=?,  attachcnt=?, replycnt=?,  cTime=?,  userID=?,  IP=?, repuserID=?,  mTime=? " +  
				"where GForumID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select gForumID,  groupID,  villageID, title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up,  good, upCnt, downCnt,  tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				" from GForum where GForumID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select gForumID,  groupID,  villageID, title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up, good, upCnt, downCnt,  tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				" from GForum");
		
		sb.append(" where GroupID in ( select GroupID from GMember where UserID=? ) " +
				"and MTime >= ? group by GroupID Order by MTime desc ");
				
		return sb.toString();
	}
	
	/**
	 * 查询按firsort分组后的前n条记录
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getGroupByFirsortSql()
	{
		StringBuffer sb =  new StringBuffer("select a.gForumID,  a.groupID,  a.villageID, a.title, a.author,  a.frsource,  a.content,  a.intro," +
				" a.fileID,  a.firsort,  a.secsort,  a.thisort,  a.status, a.type,  a.up,  a.good, a.upCnt, a.downCnt, a.tags,  a.attachcnt," +
				" a.replycnt,  a.cTime,  a.userID,  a.IP, a.repuserID,  a.mTime " +
				" from GForum a where a.villageID=? and ? > (select count(*) from GForum g where a.Firsort = g.Firsort " +
				"and g.gForumID < a.gForumID   ) order by  a.Firsort, a.mTime desc ");

		return sb.toString();	
	}		
	/**
	 * 查询按firsort分组后的前第一条记录
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getGroupByFirOneSql()
	{
		StringBuffer sb =  new StringBuffer("select a.gForumID,  a.groupID,  a.villageID, a.title, " +
				"a.author,  a.frsource,  a.content,  a.intro, a.fileID,  a.firsort,  a.secsort,  " +
				"a.thisort,  a.status, a.type,  a.up,  a.good, a.upCnt, a.downCnt, a.tags,  a.attachcnt, " +
				"a.replycnt,  a.cTime,  a.userID,  a.IP, a.repuserID,  a.mTime" +
				" from (select * from GForum order by firsort,gForumID desc) a where a.villageID=? " +
				" group by firsort ");

		return sb.toString();	
	}		
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select gForumID,  groupID,  villageID, title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up,  good, upCnt, downCnt,  tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				" from GForum ");
		
		sb.append(" where GroupID=? Order by Up, MTime desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyFolluserIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select g.gForumID,  g.groupID,  g.villageID, g.title, g.author, " +
				" g.frsource,  g.content,  g.intro, g.fileID,  g.firsort,  g.secsort,  g.thisort,  " +
				"g.status, g.type,  g.up,  g.good, g.upCnt, g.downCnt,  g.tags,  g.attachcnt," +
				" g.replycnt,  g.cTime,  g.userID,  g.IP, g.repuserID,  g.mTime " +
				" from GForum g left join GFollForum f  on g.gForumID = f.gForumID ");
		
		sb.append(" where g.GroupID=? and ( f.UserID=? or f.UpUserID=? ) Group by g.gForumID " +
				"Order by g.MTime desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyvillageIDPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select gForumID,  groupID,  villageID, title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up,  good, upCnt, downCnt,  tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				" from GForum ");
		
		sb.append(" where villageID=? Order by Up, MTime desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select gForumID,  groupID, villageID,  title, author,  frsource,  content,  intro," +
				" fileID,  firsort,  secsort,  thisort,  status, type,  up,  good, upCnt, downCnt,  tags,  attachcnt," +
				" replycnt,  cTime,  userID,  IP, repuserID,  mTime " +
				" from GForum ");
		
		return sb.toString();	
	}	
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdAttachReplycntSql()
	{
		return "update GForum  set Attachcnt=?,  Replycnt=?, upCnt=?, downCnt=? " +
				" where GForumID=? ";
	}	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from GForum where villageID=? ";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from GForum where UserID=? ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from GForum ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT " +
				"from GForum g left join GFollForum f  on g.gForumID = f.gForumID " +
				"where g.GroupID=? and ( f.UserID=? or f.UpUserID=? ) Group by g.gForumID ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
